﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Transactions;
using Model;

namespace DAL
{
    public class GoodsClassfyDal
    {
        public GoodsClassfyDal()
        {

        }
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetDt(string condition)
        {
            string sql = "select id,parentId,className,seq,adddate,(select className from GoodsClassfy where id=c.parentId) as parentName from GoodsClassfy c where 1=1 ";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += condition;
            }
            DataTable dt = DbHelperSQLite.Query(sql).Tables[0];
            return dt;
        }

        public List<MGoodsClassfy> ListClass(string condition)
        {
            List<MGoodsClassfy> list = new List<MGoodsClassfy>();
            string sql = "select id,parentId,className,seq,adddate,(select className from GoodsClassfy where id=c.parentId) as parentName from GoodsClassfy c where 1=1 ";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += condition;
            }
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql);
            while (sdr.Read())
            {
                MGoodsClassfy m = new MGoodsClassfy();
                m.Id = Convert.ToInt32(sdr["id"]);
                m.ParentId = Convert.ToInt32(sdr["parentId"]);
                m.ClassName = Convert.ToString(sdr["className"]);
                m.Seq = Convert.ToInt32(sdr["seq"]);
                m.AddDate = Convert.ToDateTime(sdr["addDate"]);
                list.Add(m);
            }
            sdr.Dispose();
            sdr.Close();
            return list;
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public int Add(DataTable dt)
        {
            string sql = "insert into GoodsClassfy(parentId,className,seq,adddate)values(@parentId,@className,0,date())";
            int succCnt = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                    new SQLiteParameter("@parentId",dt.Rows[i]["parentId"]),
                    new SQLiteParameter("@className",dt.Rows[i]["className"]),
                };
                succCnt = succCnt + DbHelperSQLite.ExecuteSql(sql, sqliteParameter);
            }
            return succCnt;
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public int Update(DataRow dr)
        {
            string sql = "update GoodsClassfy set parentId=@parentId,className=@className where id=@id";
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@parentId",dr["parentId"]),
                new SQLiteParameter("@className",dr["className"]),
                new SQLiteParameter("@id",dr["id"]),
            };
            return DbHelperSQLite.ExecuteSql(sql, sqliteParameter);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del(int id)
        {
            string sql = "delete from GoodsClassfy where id=@id";
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@id",id)
            };
            return DbHelperSQLite.ExecuteSql(sql, sqliteParameter);
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="delIds"></param>
        /// <returns></returns>
        public bool Save(List<MGoodsClassfy> list, string delIds, int sellerId)
        {
            string sql = "";
            SQLiteParameter[] sqliteParameter;
            Hashtable hash;
            //把商品里面类别在delIDs中的分类置为0

            //删除类别
            sql = "delete from goodsClassfy where id in (" + delIds + ") or parentId in(" + delIds + ") ";
            DbHelperSQLite.ExecuteSql(sql);

            DataRowCollection dr;
            int parentId = 0;
            if (list == null)
            {
                return true;
            }
            foreach (var li in list)
            {
                if (li.Id > 0)
                {
                    sql = "update goodsClassfy set className=@className,parentId=@parentId where id=@id";
                    sqliteParameter = new SQLiteParameter[]{
                        new SQLiteParameter("@className",li.ClassName),
                        new SQLiteParameter("@parentId",li.ParentId),
                        new SQLiteParameter("@id",li.Id),
                    };
                    hash = new Hashtable();
                    hash.Add(sql, sqliteParameter);
                    DbHelperSQLite.ExecuteSqlTran(hash);
                }
                else
                {
                    if (li.ParentId == -1)//如果parentid为-1，则是新增分类下面的子窗体，否则的话parentId为0，或者大于0
                    {
                        sql = "insert into goodsClassfy(parentId,className,seq,adddate,sellerId)values(@parentId,@className,0,date(),@sellerId)";
                        sqliteParameter = new SQLiteParameter[]{
                                new SQLiteParameter("@className",li.ClassName),
                                new SQLiteParameter("@parentId",parentId),
                                new SQLiteParameter("@sellerId",li.SellerId),
                            };
                        hash = new Hashtable();
                        hash.Add(sql, sqliteParameter);
                        DbHelperSQLite.ExecuteSqlTran(hash);
                    }
                    else
                    {
                        sql = "insert into goodsClassfy(parentId,className,seq,adddate,sellerId)values(@parentId,@className,0,date(),@sellerId)";
                        sqliteParameter = new SQLiteParameter[]{
                                new SQLiteParameter("@className",li.ClassName),
                                new SQLiteParameter("@parentId",li.ParentId),
                                new SQLiteParameter("@sellerId",li.SellerId),
                            };
                        hash = new Hashtable();
                        hash.Add(sql, sqliteParameter);
                        DbHelperSQLite.ExecuteSqlTran(hash);
                        if (li.ParentId == 0)//如果刚刚插入的是新增的父类，则取出刚刚插入的id
                        {
                            sql = "select max(id) from goodsClassfy where sellerId=@sellerId";
                            sqliteParameter = new SQLiteParameter[]{
                                    new SQLiteParameter("@sellerId",sellerId),
                                };
                            parentId = Convert.ToInt32(DbHelperSQLite.GetSingle(sql, sqliteParameter));
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 获得分类，按照父子排序
        /// </summary>
        /// <param name="mclass"></param>
        /// <returns></returns>
        public List<MGoodsClassfy> ListClassByGroup(MGoodsClassfy mclass)
        {
            string sql = "select id,parentId,case parentId when 0 then className else className end as className from goodsclassfy where 1=1 ";
            if (mclass.SellerId > 0)
            {
                sql += " and sellerId=@sellerId ";
            }
            if (mclass.ParentId > 0)
            {
                sql += " and parentId=@parentId ";
            }
            if (mclass.Id > 0)
            {
                sql += " and id=@id ";
            }
            sql += " order by case parentId when 0 then id else parentId+0.5 end";
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@sellerId",mclass.SellerId),
                new SQLiteParameter("@parentId",mclass.ParentId),
                new SQLiteParameter("@id",mclass.Id),
            };
            List<MGoodsClassfy> list = new List<MGoodsClassfy>();
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql, sqliteParameter);
            while (sdr.Read())
            {
                MGoodsClassfy mc = new MGoodsClassfy();
                mc.Id = Convert.ToInt32(sdr["id"]);
                mc.ParentId = Convert.ToInt32(sdr["parentId"]);
                mc.ClassName = Convert.ToString(sdr["className"]);
                list.Add(mc);
            }
            return list;
        }
    }
}
